<?php

/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2013 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel_Shared
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.9, 2013-06-02
 */


/**
 * PHPExcel_Shared_Date
 *
 * @category   PHPExcel
 * @package    PHPExcel_Shared
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
 */
class PHPExcel_Shared_Date {
	/** constants */
	const CALENDAR_WINDOWS_1900 = 1900; //	Base date of 1st Jan 1900 = 1.0
	const CALENDAR_MAC_1904 = 1904; //	Base date of 2nd Jan 1904 = 1.0

	/*
	 * Names of the months of the year, indexed by shortname
	 * Planned usage for locale settings
	 *
	 * @public
	 * @var	string[]
	 */
	public static $_monthNames = array(
			'Jan' => 'January',
			'Feb' => 'February',
			'Mar' => 'March',
			'Apr' => 'April',
			'May' => 'May',
			'Jun' => 'June',
			'Jul' => 'July',
			'Aug' => 'August',
			'Sep' => 'September',
			'Oct' => 'October',
			'Nov' => 'November',
			'Dec' => 'December',
	);

	/*
	 * Names of the months of the year, indexed by shortname
	 * Planned usage for locale settings
	 *
	 * @public
	 * @var	string[]
	 */
	public static $_numberSuffixes = array(
			'st',
			'nd',
			'rd',
			'th',
	);

	/*
	 * Base calendar year to use for calculations
	 *
	 * @private
	 * @var	int
	 */
	protected static $_excelBaseDate = self::CALENDAR_WINDOWS_1900;

	/**
	 * Set the Excel calendar (Windows 1900 or Mac 1904)
	 *
	 * @param     integer $baseDate Excel base date (1900 or 1904)
	 *
	 * @return     boolean                        Success or failure
	 */
	public static function setExcelCalendar($baseDate) {
		if (($baseDate == self::CALENDAR_WINDOWS_1900) || ($baseDate == self::CALENDAR_MAC_1904))
		{
			self::$_excelBaseDate = $baseDate;

			return true;
		}

		return false;
	} //	function setExcelCalendar()


	/**
	 * Return the Excel calendar (Windows 1900 or Mac 1904)
	 *
	 * @return     integer    Excel base date (1900 or 1904)
	 */
	public static function getExcelCalendar() {
		return self::$_excelBaseDate;
	} //	function getExcelCalendar()


	/**
	 *    Convert a date from Excel to PHP
	 *
	 * @param        long    $dateValue                   Excel date/time value
	 * @param        boolean $adjustToTimezone            Flag indicating whether $dateValue should be treated as
	 *                                                    a UST timestamp, or adjusted to UST
	 * @param        string  $timezone                    The timezone for finding the adjustment from UST
	 *
	 * @return        long        PHP serialized date/time
	 */
	public static function ExcelToPHP($dateValue = 0, $adjustToTimezone = false, $timezone = null) {
		if (self::$_excelBaseDate == self::CALENDAR_WINDOWS_1900)
		{
			$my_excelBaseDate = 25569;
			//	Adjust for the spurious 29-Feb-1900 (Day 60)
			if ($dateValue < 60)
			{
				--$my_excelBaseDate;
			}
		}
		else
		{
			$my_excelBaseDate = 24107;
		}

		// Perform conversion
		if ($dateValue >= 1)
		{
			$utcDays = $dateValue - $my_excelBaseDate;
			$returnValue = round($utcDays * 86400);
			if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX))
			{
				$returnValue = (integer)$returnValue;
			}
		}
		else
		{
			$hours = round($dateValue * 24);
			$mins = round($dateValue * 1440) - round($hours * 60);
			$secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
			$returnValue = (integer)gmmktime($hours, $mins, $secs);
		}

		$timezoneAdjustment = ($adjustToTimezone) ? PHPExcel_Shared_TimeZone::getTimezoneAdjustment($timezone, $returnValue) : 0;

		// Return
		return $returnValue + $timezoneAdjustment;
	} //	function ExcelToPHP()


	/**
	 * Convert a date from Excel to a PHP Date/Time object
	 *
	 * @param    integer $dateValue Excel date/time value
	 *
	 * @return    integer                        PHP date/time object
	 */
	public static function ExcelToPHPObject($dateValue = 0) {
		$dateTime = self::ExcelToPHP($dateValue);
		$days = floor($dateTime / 86400);
		$time = round((($dateTime / 86400) - $days) * 86400);
		$hours = round($time / 3600);
		$minutes = round($time / 60) - ($hours * 60);
		$seconds = round($time) - ($hours * 3600) - ($minutes * 60);

		$dateObj = date_create('1-Jan-1970+' . $days . ' days');
		$dateObj->setTime($hours, $minutes, $seconds);

		return $dateObj;
	} //	function ExcelToPHPObject()


	/**
	 *    Convert a date from PHP to Excel
	 *
	 * @param    mixed   $dateValue                       PHP serialized date/time or date object
	 * @param    boolean $adjustToTimezone                Flag indicating whether $dateValue should be treated as
	 *                                                    a UST timestamp, or adjusted to UST
	 * @param    string  $timezone                        The timezone for finding the adjustment from UST
	 *
	 * @return    mixed        Excel date/time value
	 *                            or boolean FALSE on failure
	 */
	public static function PHPToExcel($dateValue = 0, $adjustToTimezone = false, $timezone = null) {
		$saveTimeZone = date_default_timezone_get();
		date_default_timezone_set('UTC');
		$retValue = false;
		if ((is_object($dateValue)) && ($dateValue instanceof DateTime))
		{
			$retValue = self::FormattedPHPToExcel($dateValue->format('Y'), $dateValue->format('m'), $dateValue->format('d'), $dateValue->format('H'), $dateValue->format('i'), $dateValue->format('s'));
		}
		elseif (is_numeric($dateValue))
		{
			$retValue = self::FormattedPHPToExcel(date('Y', $dateValue), date('m', $dateValue), date('d', $dateValue), date('H', $dateValue), date('i', $dateValue), date('s', $dateValue));
		}
		date_default_timezone_set($saveTimeZone);

		return $retValue;
	} //	function PHPToExcel()


	/**
	 * FormattedPHPToExcel
	 *
	 * @param    long $year
	 * @param    long $month
	 * @param    long $day
	 * @param    long $hours
	 * @param    long $minutes
	 * @param    long $seconds
	 *
	 * @return  long                Excel date/time value
	 */
	public static function FormattedPHPToExcel($year, $month, $day, $hours = 0, $minutes = 0, $seconds = 0) {
		if (self::$_excelBaseDate == self::CALENDAR_WINDOWS_1900)
		{
			//
			//	Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
			//	This affects every date following 28th February 1900
			//
			$excel1900isLeapYear = true;
			if (($year == 1900) && ($month <= 2))
			{
				$excel1900isLeapYear = false;
			}
			$my_excelBaseDate = 2415020;
		}
		else
		{
			$my_excelBaseDate = 2416481;
			$excel1900isLeapYear = false;
		}

		//	Julian base date Adjustment
		if ($month > 2)
		{
			$month -= 3;
		}
		else
		{
			$month += 9;
			--$year;
		}

		//	Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
		$century = substr($year, 0, 2);
		$decade = substr($year, 2, 2);
		$excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $my_excelBaseDate + $excel1900isLeapYear;

		$excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;

		return (float)$excelDate + $excelTime;
	} //	function FormattedPHPToExcel()


	/**
	 * Is a given cell a date/time?
	 *
	 * @param     PHPExcel_Cell $pCell
	 *
	 * @return     boolean
	 */
	public static function isDateTime(PHPExcel_Cell $pCell) {
		return self::isDateTimeFormat($pCell->getWorksheet()->getStyle($pCell->getCoordinate())->getNumberFormat());
	} //	function isDateTime()


	/**
	 * Is a given number format a date/time?
	 *
	 * @param     PHPExcel_Style_NumberFormat $pFormat
	 *
	 * @return     boolean
	 */
	public static function isDateTimeFormat(PHPExcel_Style_NumberFormat $pFormat) {
		return self::isDateTimeFormatCode($pFormat->getFormatCode());
	} //	function isDateTimeFormat()


	private static $possibleDateFormatCharacters = 'eymdHs';

	/**
	 * Is a given number format code a date/time?
	 *
	 * @param     string $pFormatCode
	 *
	 * @return     boolean
	 */
	public static function isDateTimeFormatCode($pFormatCode = '') {
		// Switch on formatcode
		switch ($pFormatCode)
		{
			//	General contains an epoch letter 'e', so we trap for it explicitly here
			case PHPExcel_Style_NumberFormat::FORMAT_GENERAL:
				return false;
			//	Explicitly defined date formats
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYMINUS:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMMINUS:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_MYMINUS:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME1:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME2:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME5:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME6:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME7:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX16:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX17:
			case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX22:
				return true;
		}

		//	Typically number, currency or accounting (or occasionally fraction) formats
		if ((substr($pFormatCode, 0, 1) == '_') || (substr($pFormatCode, 0, 2) == '0 '))
		{
			return false;
		}
		// Try checking for any of the date formatting characters that don't appear within square braces
		if (preg_match('/(^|\])[^\[]*[' . self::$possibleDateFormatCharacters . ']/i', $pFormatCode))
		{
			//	We might also have a format mask containing quoted strings...
			//		we don't want to test for any of our characters within the quoted blocks
			if (strpos($pFormatCode, '"') !== false)
			{
				$segMatcher = false;
				foreach (explode('"', $pFormatCode) as $subVal)
				{
					//	Only test in alternate array entries (the non-quoted blocks)
					if (($segMatcher = !$segMatcher) && (preg_match('/(^|\])[^\[]*[' . self::$possibleDateFormatCharacters . ']/i', $subVal)))
					{
						return true;
					}
				}

				return false;
			}

			return true;
		}

		// No date...
		return false;
	} //	function isDateTimeFormatCode()


	/**
	 * Convert a date/time string to Excel time
	 *
	 * @param    string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
	 *
	 * @return    float|FALSE        Excel date/time serial value
	 */
	public static function stringToExcel($dateValue = '') {
		if (strlen($dateValue) < 2)
			return false;
		if (!preg_match('/^(\d{1,4}[ \.\/\-][A-Z]{3,9}([ \.\/\-]\d{1,4})?|[A-Z]{3,9}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?|\d{1,4}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?)( \d{1,2}:\d{1,2}(:\d{1,2})?)?$/iu', $dateValue))
			return false;

		$dateValueNew = PHPExcel_Calculation_DateTime::DATEVALUE($dateValue);

		if ($dateValueNew === PHPExcel_Calculation_Functions::VALUE())
		{
			return false;
		}
		else
		{
			if (strpos($dateValue, ':') !== false)
			{
				$timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($dateValue);
				if ($timeValue === PHPExcel_Calculation_Functions::VALUE())
				{
					return false;
				}
				$dateValueNew += $timeValue;
			}

			return $dateValueNew;
		}


	}

	public static function monthStringToNumber($month) {
		$monthIndex = 1;
		foreach (self::$_monthNames as $shortMonthName => $longMonthName)
		{
			if (($month === $longMonthName) || ($month === $shortMonthName))
			{
				return $monthIndex;
			}
			++$monthIndex;
		}

		return $month;
	}

	public static function dayStringToNumber($day) {
		$strippedDayValue = (str_replace(self::$_numberSuffixes, '', $day));
		if (is_numeric($strippedDayValue))
		{
			return $strippedDayValue;
		}

		return $day;
	}

}
